#!/usr/bin/env python
# encoding: utf-8
"""
    @author:水痕
    @time:2018-03-28 14:11:39
    @email:332904234@qq.com
    @version:1.0
    @fileName:12-抓取数据存入数据库.py
    @describe:
"""
import pymysql, requests
from bs4 import BeautifulSoup


# 定义一个创建数据库的函数
def create_table():
    # 创建数据库连接
    db = pymysql.connect(host='127.0.0.1', user='root', passwd='JIanshuihen128@', db='nodejs', port=3306,
                         charset='utf8')

    # 获取数据库句柄
    cursor = db.cursor()

    # cursor.execute('DROP TABLE IF EXISTS article')
    # 创建article表的sql语句
    sql = """
        create table if not exists article(
          id INT NOT NULL AUTO_INCREMENT,
          article_title VARCHAR(100) ,
          article_author VARCHAR(50) ,
          article_content text,
          PRIMARY KEY (`id`)
        )
    """

    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交事务
        db.commit()
        print('创建表成功')
    except pymysql.Error as e:
        # 数据回滚
        db.rollback()
        print(e)
    finally:
        if db:
            db.close()


# 获取网页的函数
def get_html_data(url, headers):
    response = requests.get(url, headers=headers)
    response.encoding = 'utf-8'
    soup = BeautifulSoup(response.content, "html5lib")
    article = soup.find("div", id='article_show')
    article_title = article.h1.string
    article_author = article.find('p', class_="article_author").string
    article_contents = article.find('div', class_="article_text").find_all('p')
    article_content = ''
    for content in article_contents:
        article_content = article_content + str(content)
    return article_author, article_title, article_content


# 定义一个存入数据库的函数
def insert_table(article_author, article_title, article_content):
    # 创建数据库连接
    db = pymysql.connect(host='127.0.0.1', user='root', passwd='JIanshuihen128@', db='nodejs', port=3306,
                         charset='utf8')

    # 获取数据库句柄
    cursor = db.cursor()

    # 插入数据
    query_sql = 'select * from article where article_title=%s'
    sql = 'insert into article (article_title,article_author,article_content) values (%s, %s, %s)'
    # 使用 cursor() 方法创建一个游标对象 cursor
    try:
        query_value = (article_title,)
        # 执行 sql 语句
        cursor.execute(query_sql, query_value)
        results = cursor.fetchall()
        if len(results) == 0:
            value = (article_title, article_author, article_content)
            cursor.execute(sql, value)
            # 提交事务
            db.commit()
            print('--------------《%s》 insert table success-------------' % article_title)
            return True
        else:
            print('--------------《%s》 已经存在-------------' % article_title)
            return False
    except BaseException as e:  # 如果发生错误则回滚
        db.rollback()
        print(e)
    finally:  # 关闭游标连接
        cursor.close()
        # 关闭数据库连接
        db.close()


if __name__ == "__main__":
    # 创建数据库
    # create_table()
    headers = {
        'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.162 Safari/537.36'}
    url = 'https://meiriyiwen.com/random'
    article_author, article_title, article_content = get_html_data(url=url, headers=headers)
    insert_table(article_author, article_title, article_content)
